/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pojo.Provider;

/**
 *
 * @author Mostafa
 */
public class ProviderDao {
    
   private  Connection c;

    public ProviderDao(Connection c) {
        this.c = c;
    }
    
    public void insert(Provider p)
    {
        try {
           
          PreparedStatement ps= c.prepareStatement("insert into providers (name , type , headers ,table_name ,script_path) values ( ? ,? , ? , ? ,?)");
       
          ps.setString(1, p.getName());
          ps.setString(2, p.getType());
          ps.setString(3, p.getHeaders());
          ps.setString(4, p.getTableName());
          ps.setString(5, p.getScriptPath());
         
          ps.execute();
          
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }
    
    public Provider getById(Integer id){
        try {
            Provider p=new Provider();
            PreparedStatement ps= c.prepareStatement("select * from providers where p_id=?");
            ps.setInt(1, id);
            ResultSet rset= ps.executeQuery(); 
            rset.next();
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
            
            return p;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
            return null;
    }
    
    public Provider getByName(String name){
        try {
            Provider p=new Provider();
            PreparedStatement ps= c.prepareStatement("select * from providers where name=?");
            ps.setString(1, name);
            ResultSet rset= ps.executeQuery(); 
            rset.next();
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
            
            return p;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
            return null;
    }
    
    public void update(Provider p)
    {
        try {
            boolean first=false;
            String updatetxt="";
            if(p.getName()!=null)
            {   first=true;
                updatetxt="name='"+p.getName()+"'";
            }
            if(p.getType()!=null)
            {
                if (first) {
                    updatetxt+=", ";
                }
                 first=true;
                updatetxt+="type='"+p.getType()+"'";
            }
            if(p.getHeaders()!=null)
            {
                  if (first) {
                    updatetxt+=", ";
                }
                   first=true;
                updatetxt+="headers='"+p.getHeaders()+"'";
            }
            if(p.getTableName()!=null)
            {
                  if (first) {
                    updatetxt+=", ";
                }
                   first=true;
                updatetxt+="table_name='"+p.getTableName()+"'";
            }
            if(p.getScriptPath()!=null)
            {
                
                  if (first) {
                    updatetxt+=", ";
                }
                   first=true;
                updatetxt+="script_path='"+p.getScriptPath()+"'";
            }
        
            PreparedStatement ps=c.prepareStatement("update providers set "+updatetxt+" where p_id=?");
            ps.setInt(1, p.getPId());
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }
            
    public void deleteByName(String name)
    {
        try {
            PreparedStatement ps=c.prepareStatement("delete from providers where name=?");
            ps.setString(1, name);
            ps.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
       public void deleteById(Integer id)
    {
        try {
            PreparedStatement ps=c.prepareStatement("delete from providers where p_id=?");
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public static void main(String [] args){
        try {
            String url ="jdbc:mysql://localhost:3306/dimensionproject";
               DriverManager.registerDriver(new com.mysql.jdbc.Driver() );
             Connection c=DriverManager.getConnection(url, "root", "root");
             Provider p=new Provider();
            
             p.setName("mohsen");
             p.setType("book");
            new ProviderDao(c).insert(p);
            System.out.println("insert .. . . .");
            
             
          
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }
    
}
